package model;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;

public class ClienteDAO
{
	public void incluir(Cliente cliente) throws SQLException 
	{
		Statement comando = DBConnection.getInstancia().conn.createStatement();
		String query = "INSERT INTO cliente VALUES('" + cliente.getCnpj() + "','" + cliente.getRazaoSocial() + "')";
		comando.executeUpdate(query);
	}

	public void alterar(Cliente cliente) throws SQLException 
	{
		Statement comando = DBConnection.getInstancia().conn.createStatement();
		String query = "UPDATE cliente SET razao_social = '" + cliente.getRazaoSocial() + "' WHERE cnpj = '" + cliente.getCnpj() + "'";
		comando.executeUpdate(query);
	}

	public boolean excluir(Cliente cliente) throws SQLException 
	{
		Statement comando = DBConnection.getInstancia().conn.createStatement();
		ResultSet rs;
		boolean retorno = true;
		int qtdDependentes = 0;
		String query = "";

		query = "SELECT COUNT(*) as projetos FROM projeto WHERE cnpj = '" + cliente.getCnpj() + "'";
		rs = comando.executeQuery(query);
		if (rs.next())
			qtdDependentes += rs.getInt("projetos");

		if (qtdDependentes > 0)
			retorno = false;
		else
		{
			query = "DELETE FROM cliente WHERE cnpj = '" + cliente.getCnpj() + "'";
			comando.executeUpdate(query);
		}
		return retorno;
	}

	public ArrayList<Cliente> consultar(Cliente clienteFiltro, boolean peloCNPJ) throws SQLException
	{
		Statement comando = DBConnection.getInstancia().conn.createStatement();
		ArrayList<Cliente> retorno = new ArrayList<Cliente>();
		ResultSet rs;
		try 
		{
			String query = "SELECT * FROM cliente WHERE 1 = 1";
			if (clienteFiltro != null)
			{
				if (peloCNPJ)
					query += " AND cnpj = '" + clienteFiltro.getCnpj() + "'";
				else
				{
					if (!(null == clienteFiltro.getCnpj()))
						query += " AND cnpj LIKE '%" + clienteFiltro.getCnpj() + "%'";
					if (!(null == clienteFiltro.getRazaoSocial()))
						query += " AND lower(razao_social) LIKE lower('%" + clienteFiltro.getRazaoSocial() + "%')";
				}
			}
			query += " ORDER BY razao_social";
			rs = comando.executeQuery(query);
			while (rs.next())
			{
				Cliente c = new Cliente();
				c.setCnpj(rs.getString("cnpj"));
				c.setRazaoSocial(rs.getString("razao_social"));
				retorno.add(c);
			}
		} 
		catch (SQLException e) 
		{
			e.printStackTrace();
		}
		return retorno;
	}
}